var express = require('express');
var router = express.Router();
const Query = require('../tools/db');
const { image_url } = require('../config/config');


//获取城市地区信息
router.get('/areas', async function (req, res, next) {
  let sql = `SELECT *FROM e_area`
  let [err, result] = await Query(sql);
  //错误判断
  if (err) {
    next('服务器内部错误');
    return;
  }
  res.send({
    code: 200,
    msg: '获取成功',
    result,
  })
});
//获取年级信息
router.get('/grade', async function (req, res, next) {
  let sql = `SELECT *FROM e_grade`
  let [err, result] = await Query(sql);
  //错误判断
  if (err) {
    next('服务器内部错误');
    return;
  }
  res.send({
    code: 200,
    msg: '获取成功',
    result,
  })
});
//获取学科信息
router.get('/subject', async function (req, res, next) {
  let sql = `SELECT *FROM e_subject`
  let [err, result] = await Query(sql);
  //错误判断
  if (err) {
    next('服务器内部错误');
    return;
  }
  res.send({
    code: 200,
    msg: '获取成功',
    result,
  })
});
//获取版本信息
router.get('/type', async function (req, res, next) {
  let sql = `SELECT *FROM e_type`
  let [err, result] = await Query(sql);
  //错误判断
  if (err) {
    next('服务器内部错误');
    return;
  }
  res.send({
    code: 200,
    msg: '获取成功',
    result,
  })
});

//获取课程列表数据
router.get('/courses', async function (req, res, next) {
  /*
    分页原理
    如果每页3条，共10条数据
    let pageSize = 3	每页条数  	传参
    let page = 1 		当前的页码	传参

    let totalRow = 10	总条数  	 数据库查
    let totalPage = Math.ceil(totalRow/pageSize)  => 4 总页数
    
    第1页： LIMIT 0,pageSize    0 => (page-1)*pageSize
    第2页： LIMIT 3,pageSize    3 => (page-1)*pageSize
    第3页： LIMIT 6,pageSize    6 => (page-1)*pageSize
    第4页： LIMIT 9,pageSize    9 => (page-1)*pageSize
 */
  //1.------------获取前端传输的参数
  //pageSize:每页条数  page：页数   area：地区   grade：年级        subject：学科    type：版本
  let { pageSize = 6, page = 1, area, grade, subject, type } = req.query;
  let arr = [];//声明一个空数组，作为参数仓库
  let str = '';//声明一个空字符串，作为参数拼接器使用
  if (area) {
    arr.push(`area_name='${area}'`)
  }
  if (grade) {
    arr.push(`grade_name='${grade}'`)
  }
  if (subject) {
    arr.push(`subject_name='${subject}'`)
  }
  if (type) {
    arr.push(`type_name='${type}'`)
  }
  //2.----------如果arr有长度，代表前端传入了参数，将数组项使用AND与WHERE拼接到字符串
  if (arr.length) {
    str += 'WHERE' + arr.join('AND')
  }


  //3.--------查总课程数的条数
  let sql1 = `SELECT *FROM e_course ${str}`;
  let [err1, result1] = await Query(sql1);
  if (err1) {
    next('服务器内部错误')
    return;
  }
  let totalRow = result1.length;//总条数
  let totalPage = Math.ceil(totalRow / pageSize);//总页数
  //4.---------获取对应页的数据
  let sql2 = `SELECT id,cid,subject_name,title,price,area_name,type_name,CONCAT('${image_url}','uploads/banner/',image_src) AS image_src FROM e_course ${str} ORDER BY id DESC LIMIT ${(page - 1) * pageSize},${pageSize}`
  let [err2, result2] = await Query(sql2);
  if (err2) {
    next('服务器内部错误')
    return;
  }
  //5.------导出数据
  res.send({
    code: 200,
    msg: '获取成功',
    result: {
      data: result2,
      page,//当前页数
      totalPage,//总页数
      totalRow,//总条数
    }
  })
});
//搜索框接口
router.get('/searchCourse', async function (req, res, next) {
  //1.获取前端传输的参数
  let { pageSize = 6, page = 1, key } = req.query;
  if (!key) {
    next('必须传入搜索关键字');
    return;
  }
  //查总课程数的条数
  let sql1 = `SELECT *FROM e_course WHERE title LIKE '%${key}%'`;
  let [err1, result1] = await Query(sql1);
  if (err1) {
    next('服务器内部错误')
    return;
  }
  let totalRow = result1.length;//总条数
  let totalPage = Math.ceil(totalRow / pageSize);//总页数
  //获取对应页的数据
  let sql2 = `SELECT id,cid,subject_name,title,price,area_name,type_name,CONCAT('${image_url}','uploads/banner/',image_src) AS image_src FROM e_course WHERE title LIKE '%${key}%' ORDER BY id DESC LIMIT ${(page - 1) * pageSize},${pageSize}`

  let [err2, result2] = await Query(sql2);
  if (err2) {
    next('服务器内部错误')
    return;
  }
  res.send({
    code: 200,
    msg: '获取成功',
    result: {
      data: result2,
      page,//当前页数
      totalPage,//总页数
      totalRow,//总条数
    }
  })
});
module.exports = router;
